The SQL query language provides a user with the ability to query (and modify) tabular data stored using the relational data model. The relational data model dictates that each cell in a table (a column of a row) is a single scalar (or atomic) value. The structured query language (SQL) is an American National Standards Institute (ANSI) standard used to communicate with a relational database. SQL statements are used to perform tasks such as update data or retrieve data from a relational database. Although many database systems use SQL, many of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish many things that one desires to do with a relational database and are believed to be well known to those skilled in the database art.
Recent extensions to the relational data model relax the restriction of storing a single scalar value in a cell and allow each cell in a table to be a collection of values itself. Each element inside this collection can in turn contain a collection itself, i.e. the collection can be nested to arbitrary levels of depth. Tables with such collection-valued cells with multiple values can be broadly called nested tables. This development presents the problem of how to extend the SQL language to modify the collection values in such a nested table. For example, a technique for modifying the collection-valued cells to permit the simultaneous change of scalar values at any nesting level is desired.
Current implementations of nested tables and associated standards offer extensions for querying and updating collections in a nested table. In a conventional nested table system, however, the nested scalar values are updated one level at a time for each level of scalar values in a collection-valued cell, thus requiring multiple updates to change the stored values in a multi-level cell. For example, U.S. Pat. No. 6,564,203 describes techniques for updating data that logically resides in one or more nested collections in a nested collection column. A Data Manipulation Language (DML) statement that operates on one or more of the nested collections is detected and trigger code is executed once for each individual element within the nested collections targeted by the DML statement. The trigger code may include references to parent values; however, the triggers are designed to fire when a row of a database table or a table view is updated, inserted or deleted. In other words, only a single row is updated at a time. This process is slow and tedious and does not permit updates at any designated nested level.
U.S. Pat. No. 6,493,708 discloses a mechanism for defining a hierarchical dimension, the various levels thereof, and the relationships among the levels using a CREATE DIMENSION statement that includes various LEVEL statements to identify the levels of the hierarchy. A dimension table embeds the hierarchical relationships between granules in various levels of the dimension, assuming that the levels go from a finer to a coarser level. However, no general mechanism is taught for updating nested tables for values in respective rows that are not necessarily related to each other as finer/coarser representations of the same data.
The SQL 2003 standard specifies two kinds of collections—arrays and multisets. Columns of a table can be one of these two types. In existing database systems that implement arrays and multisets, the standard specifies syntax and semantics for modifying the collection-valued column in its entirety where the old value is replaced by an entirely new value. The prior art does not specify any way to modify the collection-valued column incrementally. A technique is desired that permits one to perform incremental modifications to such collections since it can be very expensive to replace an entire large collection with a new collection while the need is to only modify a small portion of it. The present invention addresses these needs in the art.